﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;
using System.Web;
using System.Web.Services;

namespace Ortodoctor.modelo
{
    public class modeloOrcamento : conexao
    {
        public modeloOrcamento()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Orcamento o)
        {
            try
            {
                //con.open();
                String sql = "INSERT INTO orcamento(id_cliente, id_usuario, tempo_tratamento, " +
                             "valor_total, valor_parcela, id_procedimento) VALUES (:p1, :p2, :p3, :p4, :p5, :p6)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", o.IdCliente);
                com.Parameters.AddWithValue("p2", o.IdUsuario);
                com.Parameters.AddWithValue("p3", o.TempoTratamento);
                com.Parameters.AddWithValue("p4", o.ValorTotal);
                com.Parameters.AddWithValue("p5", o.ValorParcela);
                com.Parameters.AddWithValue("p6", o.IdProcedimento);

                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
            return getMaxId();
        }

        public int getMaxId()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "select MAX(ID_ORCAMENTO) as id_orcamento FROM ORCAMENTO";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }

            return Convert.ToInt32(dt.Rows[0]["id_orcamento"]);
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select * from orcamento inner join 
	                        cliente using(id_cliente) inner join
	                        usuario using(id_usuario) inner join
	                        procedimento using(id_procedimento)
	                        ";
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = @"select * from orcamento inner join 
	                        cliente using(id_cliente) inner join
	                        usuario using(id_usuario) inner join
	                        procedimento using(id_procedimento) where cliente.id_clinica=" + HttpContext.Current.Session["ID_CLINICA"];
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getClientesComOrcamento()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select distinct id_cliente, nome from orcamento inner join 
	                        cliente using(id_cliente) 
	                        ";
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = @"select distinct id_cliente, nome from orcamento inner join 
	                        cliente using(id_cliente) where cliente.id_clinica =" + HttpContext.Current.Session["ID_CLINICA"];
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }


        public DataTable getOrcamentosComClientes()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = string.Format(@"select id_cliente, id_procedimento, nome_procedimento from orcamento 
                                                        inner join procedimento 
                                                using(id_procedimento) inner join cliente using(id_cliente)");
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = string.Format(@"select id_cliente, id_procedimento, nome_procedimento from orcamento 
                                                        inner join procedimento 
                                                using(id_procedimento) inner join cliente using(id_cliente) where cliente.id_clinica=" + HttpContext.Current.Session["ID_CLINICA"]);
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrcamento(int idOrcamento)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"SELECT *  from orcamento inner join 
	                        cliente using(id_cliente) inner join
	                        usuario using(id_usuario) inner join
	                        procedimento using(id_procedimento) WHERE id_orcamento=:id ";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", idOrcamento);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrcamentoPorCliente(int idCliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM orcamento INNER JOIN cliente using(id_cliente) " +
                         "INNER JOIN usuario using (id_usuario) WHERE id_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", idCliente);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrcamentoPorUsuario(int idUsuario)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM orcamento INNER JOIN cliente using(id_cliente) " +
                         "INNER JOIN usuario using (id_usuario) WHERE id_usuario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", idUsuario);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Orcamento o)
        {
            try
            {
                //con.open();
                String sql = "UPDATE orcamento SET tempo_tratamento=:t, id_usuario=:idu, valor_total=:v, id_usuario_modificacao=:id_mod, valor_parcela=:v2, id_procedimento=:proc WHERE" +
                             " id_orcamento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("t", o.TempoTratamento);
                com.Parameters.AddWithValue("v", o.ValorTotal);
                com.Parameters.AddWithValue("idu", o.IdUsuario);
                com.Parameters.AddWithValue("v2", o.ValorParcela);

                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("id", o.IdOrcamento);
                com.Parameters.AddWithValue("proc", o.IdProcedimento);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM orcamento WHERE id_orcamento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "update orcamento_hist set id_usuario_modificacao = :id_mod, data_modificacao=:data where id_orcamento=:id";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id_cliente, int id_procedimento)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM orcamento WHERE id_cliente=:id_cliente and id_procedimento = :id_procedimento";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id_cliente", id_cliente);
                com.Parameters.AddWithValue("id_procedimento", id_procedimento);
                com.ExecuteNonQuery();


                sql = "update orcamento_hist set id_usuario_modificacao = :id_mod, data_modificacao=:data where id_cliente=:id_cliente and id_procedimento = :id_procedimento";
                com = new NpgsqlCommand(sql, conn);

                com.Parameters.AddWithValue("id_cliente", id_cliente);
                com.Parameters.AddWithValue("id_procedimento", id_procedimento);
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO orcamento_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        internal DataTable getOrcamentoTotal(int id_cliente, int id_procedimento)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select * from orcamento where 
                                        id_cliente = :id_cliente and
                                        id_procedimento = :id_procedimento";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id_cliente", id_cliente);
                com.Parameters.AddWithValue("id_procedimento", id_procedimento);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
    }
}
